﻿/* ***********************************************
 * Author		:  kingthy
 * Email		:  kingthy@gmail.com
 * Description	:  DbCommandExecutor
 *
 * ***********************************************/

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using KT.Core.Extensions;
using System.Reflection;
using System.ComponentModel;
using System.Data.SqlClient;
namespace KT.Framework.Database
{
    /// <summary>
    /// 数据命令执行者
    /// </summary>
    public class DbCommandExecutor
    {
        /// <summary>
        /// 根据DbConnection对象实例化
        /// </summary>
        /// <param name="connection"></param>
        public DbCommandExecutor(DbConnection connection)
            : this(new DbHelper(connection))
        {}

        /// <summary>
        /// 根据数据库帮助对象实例化
        /// </summary>
        /// <param name="dbHelper"></param>
        public DbCommandExecutor(DbHelper dbHelper)
        {
            this.DbHelper = dbHelper;
        }

        /// <summary>
        /// 返回此数据命令执行者所使用的DbHelper实例
        /// </summary>
        public DbHelper DbHelper { get; private set; }

        #region 执行语句并返回自增标识的值
        /// <summary>
        /// 获取插入数据后返回标识值的命令语句
        /// </summary>
        /// <returns></returns>
        protected virtual DbCommand GetIdentityCommand(DbCommand command)
        {
            string idCommandText = "SELECT @@IDENTITY";
            if (typeof(SqlCommand).IsInstanceOfType(command))
            {
                idCommandText = "SELECT SCOPE_IDENTITY()";
                //MSSQL数据库
                if (command.CommandType == CommandType.Text)
                {
                    command.CommandText = string.Concat(command.CommandText, ";", idCommandText);
                    return command;
                }
            }
            var cmd = command.Connection.CreateCommand();
            if (command.Transaction != null)
                cmd.Transaction = command.Transaction;
            cmd.CommandText = idCommandText;
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = command.CommandTimeout;
            return cmd;
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        protected long ExecuteIdentity(DbCommandWrapped command)
        {
            using (command)
            {
                return this.ExecuteIdentity(command.Command, command.KeepConnection);
            }
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        public long ExecuteIdentity(DbCommand command)
        {
            return this.ExecuteIdentity(command, false);
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="command"></param>
        /// <param name="keepConnection">数据数据连接初始是关闭状态的，则设置是否在执行完命令后是否恢复原关闭状态还是保持数据连接的打开状态</param>
        /// <returns></returns>
        protected virtual long ExecuteIdentity(DbCommand command, bool keepConnection)
        {
            long id = 0;
            bool isClosed = command.Connection.State == ConnectionState.Closed;

            if (isClosed) this.DbHelper.OpenConnection(command.Connection);

            //获取Identity
            var cmd = this.GetIdentityCommand(command);
            if (cmd == command)
            {
                //同一个命令，则直接获取
                id = cmd.ExecuteScalar().As<long>();
            }
            else
            {
                //独立的命令，则先执行操作
                int r = command.ExecuteNonQuery();
                if (r > 0 && cmd != null)
                {
                    id = cmd.ExecuteScalar().As<long>();
                }
            }
            if (isClosed && !keepConnection) command.Connection.Close();

            return id;
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public long ExecuteIdentity(string commandText)
        {
            return this.ExecuteIdentity(this.DbHelper.CreateDbCommandWrapped(commandText));
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public long ExecuteIdentity(CommandType commandType, string commandText)
        {
            return this.ExecuteIdentity(this.DbHelper.CreateDbCommandWrapped(commandType, commandText));
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public long ExecuteIdentity(string commandText, params DbParameter[] parameters)
        {
            return this.ExecuteIdentity(this.DbHelper.CreateDbCommandWrapped(commandText, parameters));
        }
        /// <summary>
        /// 执行命令并返回标识
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="commandText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public long ExecuteIdentity(CommandType commandType, string commandText, params DbParameter[] parameters)
        {
            return this.ExecuteIdentity(this.DbHelper.CreateDbCommandWrapped(commandType, commandText, parameters));
        }
        #endregion

        #region 获取数据 GetRowData
        /// <summary>
        /// 通过Id数据获取行数据对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual T GetRow<T>(string tableName, long id)
            where T : DbObject, new()
        {
            return this.GetRow<T>(tableName, string.Format("{0}={1}", this.DbHelper.QuoteIdentifier("Id"), id));
        }
        /// <summary>
        /// 通过条件获取行数据对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public T GetRow<T>(string tableName, string condition)
            where T : DbObject, new()
        {
            return this.GetRow<T>(tableName, condition, null);
        }
        /// <summary>
        /// 通过条件获取行数据对象
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public virtual T GetRow<T>(string tableName, string condition, params DbParameter[] pars)
            where T : DbObject, new()
        {
            StringBuilder buffer = new StringBuilder();
            buffer.AppendFormat("SELECT * FROM {0}", this.DbHelper.QuoteIdentifier(tableName));
            if (!string.IsNullOrEmpty(condition))
            {
                buffer.Append(" WHERE ");
                buffer.Append(condition);
            }
            return this.DbHelper.ExecuteDbObject<T>(buffer.ToString(), pars);
        }
        #endregion

        #region 获取数据行集 GetRowList
        /// <summary>
        /// 获取某个表的所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public List<T> GetRowList<T>(string tableName)
            where T : DbObject, new()
        {
            return this.GetRowList<T>(tableName, null, null);
        }
        /// <summary>
        /// 获取某个表的所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public List<T> GetRowList<T>(string tableName, string condition)
            where T : DbObject, new()
        {
            return this.GetRowList<T>(tableName, condition, null);
        }
        /// <summary>
        /// 获取某个表的所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public List<T> GetRowList<T>(string tableName, string condition, string orderBy)
            where T : DbObject, new()
        {
            return this.GetRowList<T>(tableName, condition, orderBy, null);
        }
        /// <summary>
        /// 获取某个表的所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="pars">条件参数</param>
        /// <returns></returns>
        public virtual List<T> GetRowList<T>(string tableName, string condition, string orderBy, params DbParameter[] pars)
            where T : DbObject, new()
        {
            StringBuilder buffer = new StringBuilder();
            buffer.AppendFormat("SELECT * FROM {0}", this.DbHelper.QuoteIdentifier(tableName));
            if (!string.IsNullOrEmpty(condition))
            {
                buffer.AppendFormat(" WHERE {0}", condition);
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                buffer.AppendFormat(" ORDER BY {0}", orderBy);
            }
            return this.DbHelper.ExecuteDbObjectList<T>(buffer.ToString(), pars);
        }
        #endregion

        #region 获取有限制的数据行集 GetRowList
        /// <summary>
        /// 获取某个表中一定数量的行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="quantity">数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public List<T> GetRowList<T>(int quantity, string tableName, string condition)
            where T : DbObject, new()
        {
            return this.GetRowList<T>(quantity, tableName, condition, null);
        }
        /// <summary>
        /// 获取某个表中一定数量的行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="quantity">数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns></returns>
        public List<T> GetRowList<T>(int quantity, string tableName, string condition, string orderBy)
            where T : DbObject, new()
        {
            return this.GetRowList<T>(quantity, tableName, condition, orderBy, null);
        }
        /// <summary>
        /// 获取某个表中一定数量的行数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="quantity">数量</param>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="orderBy">排序</param>
        /// <param name="pars">条件参数</param>
        /// <returns></returns>
        public virtual List<T> GetRowList<T>(int quantity, string tableName, string condition, string orderBy, params DbParameter[] pars)
            where T : DbObject, new()
        {
            StringBuilder buffer = new StringBuilder();
            if (quantity > 0)
            {
                buffer.AppendFormat("SELECT TOP {0} * FROM {1}", quantity, this.DbHelper.QuoteIdentifier(tableName));
            }
            else
            {
                buffer.AppendFormat("SELECT * FROM {0}", this.DbHelper.QuoteIdentifier(tableName));
            }
            if (!string.IsNullOrEmpty(condition))
            {
                buffer.AppendFormat(" WHERE {0}", condition);
            }
            if (!string.IsNullOrEmpty(orderBy))
            {
                buffer.AppendFormat(" ORDER BY {0}", orderBy);
            }
            return this.DbHelper.ExecuteDbObjectList<T>(buffer.ToString(), pars);
        }
        #endregion

        #region 移除数据行 RemoveRow
        /// <summary>
        /// 通过Id集合移除数据行,并返回已移除的数据记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tableName"></param>
        /// <param name="Ids"></param>
        /// <returns></returns>
        public virtual List<T> RemoveRow<T>(string tableName, params long[] Ids)
            where T : DbObject, new()
        {
            string ids = string.Join<long>(",", Ids);
            return this.RemoveRow<T>(tableName, string.Format("{0} IN ({1})",this.DbHelper.QuoteIdentifier("Id"), ids));
        }
        /// <summary>
        /// 通过条件移除数据行,并返回已移除的数据记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public List<T> RemoveRow<T>(string tableName, string condition)
            where T : DbObject, new()
        {
            return this.RemoveRow<T>(tableName, condition, null);
        }
        /// <summary>
        /// 通过条件移除数据行,并返回已移除的数据记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public virtual List<T> RemoveRow<T>(string tableName, string condition, params DbParameter[] pars)
            where T : DbObject, new()
        {
            //获取数据
            List<T> rows = this.GetRowList<T>(tableName, condition, null, pars);
            this.DeleteRow(tableName, condition, pars);
            return rows;
        }
        #endregion

        #region 删除数据行 DeleteRow
        /// <summary>
        /// 通过Id集合删除数据行并返回受影响的行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="ids"></param>
        /// <returns></returns>
        public virtual int DeleteRow(string tableName, params long[] ids)
        {
            return this.DeleteRow(tableName, string.Format("{0} IN ({1})", this.DbHelper.QuoteIdentifier("Id"), string.Join<long>(",", ids)));
        }
        /// <summary>
        /// 通过条件删除数据行并返回受影响的行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteRow(string tableName, string condition)
        {
            return this.DeleteRow(tableName, condition, null);
        }
        /// <summary>
        /// 通过条件删除数据行并返回受影响的行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="condition"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public virtual int DeleteRow(string tableName, string condition, params DbParameter[] pars)
        {
            //删除数据
            StringBuilder buffer = new StringBuilder();
            buffer.AppendFormat("DELETE FROM {0}", this.DbHelper.QuoteIdentifier(tableName));
            if (!string.IsNullOrEmpty(condition))
            {
                buffer.Append(" WHERE ");
                buffer.Append(condition);
            }
            return this.DbHelper.ExecuteNonQuery(buffer.ToString(), pars);
        }
        #endregion

        #region 判断是否存在某些数据行
        /// <summary>
        /// 判断是否存在某些数据行
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public bool ExistRow(string tableName, string condition)
        {
            return this.ExistRow(tableName, condition, null);
        }
        /// <summary>
        /// 判断是否存在某些数据行
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="pars">条件参数</param>
        /// <returns></returns>
        public virtual bool ExistRow(string tableName, string condition, params DbParameter[] pars)
        {
            StringBuilder buffer = new StringBuilder();
            buffer.AppendFormat("SELECT 1 FROM {0}", this.DbHelper.QuoteIdentifier(tableName));
            if (!string.IsNullOrEmpty(condition))
            {
                buffer.AppendFormat(" WHERE {0}", condition);
            }
            using (IDataReader reader = this.DbHelper.ExecuteReader(buffer.ToString(), pars))
            {
                return reader.Read();
            }
        }
        #endregion

        #region 插入与更新数据
        /// <summary>
        /// 将某个对象插入某个数据表中
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">要插入的对象。此对象中的属性或字段有DbFieldAttribute特性标记才会进行插入</param>
        public bool Insert(string tableName, object obj)
        {
            return this.Insert(tableName, obj, DbFieldAttribute.EmptyGroup);
        }
        /// <summary>
        /// 将某个对象插入某个数据表中
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">要插入的对象。此对象中的属性或字段有DbFieldAttribute特性标记才会进行插入</param>
        /// <param name="groups">更新字段的组，如果有多个组，则各组之间可用“值的或操作”处理，表示只要字段属于其中的一组即符合更新组.
        /// 注1：如果为空(DbFieldAttribute.EmptyGroup)则表示所有字段。
        /// 注2：如果为空(DbFieldAttribute.NoGroup)则表示所有空组字段。
        /// </param>
        public virtual bool Insert(string tableName, object obj, long groups)
        {
            var dbCommandBuilder = new DbCommandBuilder(tableName, this.DbHelper);
            PropertyDescriptor idProperty = null;

            var dbfiledType = typeof(DbFieldAttribute);
            
            var properties = TypeDescriptor.GetProperties(obj);
            if (properties.Count == 0) return false;
            foreach (PropertyDescriptor pro in properties)
            {
                var attr = (DbFieldAttribute)pro.Attributes[dbfiledType];
                if (attr != null)
                {
                    if (attr.Identity)
                    {
                        //标识字段
                        idProperty = pro;
                    }
                    else if (attr.AllowOperation(groups, true))
                    {
                        string name = string.IsNullOrEmpty(attr.Name) ? pro.Name : attr.Name;
                        dbCommandBuilder.AddField(name, this.DbHelper.CreateDbParameter(name,
                            attr.GetDbType(pro.PropertyType),
                            attr.Size,
                            pro.GetValue(obj)));

                    }
                }
            }

            long r = 0;

            if (dbCommandBuilder.HasFields)
            {
                if (idProperty != null)
                {
                    //有标识字段
                    r = this.ExecuteIdentity(dbCommandBuilder.InsertCommandText, dbCommandBuilder.Parameters);
                    if (r > 0)
                    {
                        object v = r;
                        if (idProperty.PropertyType != typeof(long))
                            v = r.As(idProperty.PropertyType, r);
                        idProperty.SetValue(obj, v);
                    }
                }
                else
                {
                    r = dbCommandBuilder.Insert();
                }
            }
            return r > 0;
        }
        /// <summary>
        /// 将某个对象更新到某个数据表中
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">要更新的对象。此对象中的属性或字段有DbFieldAttribute特性标记才会进行更新</param>
        public bool Update(string tableName, object obj)
        {
            return this.Update(tableName, obj, DbFieldAttribute.EmptyGroup);
        }
        /// <summary>
        /// 将某个对象更新到某个数据表中
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="obj">要更新的对象。此对象中的属性或字段有DbFieldAttribute特性标记才会进行更新</param>
        /// <param name="groups">更新字段的组，如果有多个组，则各组之间可用“值的或操作”处理，表示只要字段属于其中的一组即符合更新组.
        /// 注1：如果为空(DbFieldAttribute.EmptyGroup)则表示所有字段。
        /// 注2：如果为空(DbFieldAttribute.NoGroup)则表示所有空组字段。
        /// </param>
        public virtual bool Update(string tableName, object obj, long groups)
        {
            var dbCommandBuilder = new DbCommandBuilder(tableName, this.DbHelper);
            var dbfiledType = typeof(DbFieldAttribute);

            var properties = TypeDescriptor.GetProperties(obj);
            if (properties.Count == 0) return false;
            foreach (PropertyDescriptor pro in properties)
            {
                var attr = (DbFieldAttribute)pro.Attributes[dbfiledType];
                if (attr != null)
                {
                    string name = string.IsNullOrEmpty(attr.Name) ? pro.Name : attr.Name;
                    if (!attr.Identity && attr.AllowOperation(groups, false))
                    {
                        dbCommandBuilder.AddField(name, this.DbHelper.CreateDbParameter(name,
                            attr.GetDbType(pro.PropertyType),
                            attr.Size,
                            pro.GetValue(obj)));

                    }
                    if (attr.PrimaryKey)
                    {
                        //主键做为更新条件
                        dbCommandBuilder.AddCondition(name, this.DbHelper.CreateDbParameter(name,
                            attr.GetDbType(pro.PropertyType),
                            attr.Size,
                            pro.GetValue(obj)));
                    }
                }
            }
            return dbCommandBuilder.Update() > 0;
        }
        #endregion
    }
}
